package org.ytor.core.sqlflow.builder.select;

import org.ytor.common.util.Strs;
import org.ytor.common.util.cache.C;
import org.ytor.core.sqlflow.Page;
import org.ytor.core.sqlflow.SFunction;
import org.ytor.core.sqlflow.builder.AbsBuilder;
import org.ytor.core.sqlflow.builder.support.HavingBuilder;
import org.ytor.core.sqlflow.builder.support.JoinBuilder;
import org.ytor.core.sqlflow.builder.support.JoinOnBuilder;
import org.ytor.core.sqlflow.builder.support.WhereBuilder;
import org.ytor.core.sqlflow.executor.DQLResult;
import org.ytor.core.sqlflow.executor.SQLRuntime;
import org.ytor.core.sqlflow.log.SqlLogHelper;
import org.ytor.core.sqlflow.plugin.Interceptor;
import org.ytor.core.sqlflow.plugin.InterceptorManager;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.function.Supplier;

/**
 * created by yangtong on 2025/8/7 20:10:10
 * <br/>
 * select 构建，惰性构建
 */
public class SelectBuilder extends AbsBuilder
        implements ISelect, SelectStage, FromStage, WhereStage, GroupByStage, HavingStage,
        OrderStage, LimitStage, OffsetStage, Terminable {

    /**
     * 查询字段, 惰性解析：方法引用与原生字段分开存，buildSql 时再解析
     */
    private final List<SFunction<?, ?>> selectFnFields = new ArrayList<>();
    private final List<String> selectRawFields = new ArrayList<>();
    private List<String> finalSelectFields = null;

    /**
     * 主表
     */
    private Class<?> mainTable;

    /**
     * 主表名称
     */
    private String mainTableName;

    /**
     * 主表(子查询)
     */
    private SelectBuilder mainTableSubQuery;

    /**
     * 主表别名
     */
    private String mainAlias;
    /**
     * 表别名的数量
     */
    private int aliasCounter = 1;
    /**
     * 所有连表信息
     */
    private final List<JoinBuilder> joins = new ArrayList<>();

    /**
     * 有序参数
     */
    private final List<Object> orderedParams = new ArrayList<>();

    /**
     * where 子句
     */
    private WhereBuilder where;

    /**
     * having 子句
     */
    private HavingBuilder havingBuilder;

    /**
     * 分组的字段集合,支持 SFunction 与 String（raw）混合，惰性解析
     */
    private final List<Object> groupByItems = new ArrayList<>(); // item 为 SFunction<?,?> 或 String


    /**
     * 排序字段，以及每个字段是升序还是降序
     * @param field  SFunction<?,?> 或 String
     * @param dir  ASC / DESC
     */
    private record OrderItem(Object field, String dir) {
    }

    // 排序字段
    private final List<OrderItem> orderItems = new ArrayList<>();

    /**
     * limit
     */
    private Integer limit;
    /**
     * 偏移量
     */
    private Integer offset;

    // ============================ SELECT 字段选择 ============================

    @Override
    public <A> SelectStage select(SFunction<A, ?> field1) {
        Collections.addAll(selectFnFields, field1);
        return this;
    }

    @Override
    public <A, B> SelectStage select(SFunction<A, ?> field1, SFunction<B, ?> field2) {
        Collections.addAll(selectFnFields, field1, field2);
        return this;
    }

    @Override
    public <A, B, C> SelectStage select(SFunction<A, ?> field1, SFunction<B, ?> field2, SFunction<C, ?> field3) {
        Collections.addAll(selectFnFields, field1, field2, field3);
        return this;
    }

    @Override
    public <A, B, C, D> SelectStage select(SFunction<A, ?> field1, SFunction<B, ?> field2, SFunction<C, ?> field3, SFunction<D, ?> field4) {
        Collections.addAll(selectFnFields, field1, field2, field3, field4);
        return this;
    }

    @Override
    public <A, B, C, D, E> SelectStage select(SFunction<A, ?> field1, SFunction<B, ?> field2, SFunction<C, ?> field3, SFunction<D, ?> field4, SFunction<E, ?> field5) {
        Collections.addAll(selectFnFields, field1, field2, field3, field4, field5);
        return this;
    }

    @Override
    public <A, B, C, D, E, F> SelectStage select(SFunction<A, ?> field1, SFunction<B, ?> field2, SFunction<C, ?> field3, SFunction<D, ?> field4, SFunction<E, ?> field5, SFunction<F, ?> field6) {
        Collections.addAll(selectFnFields, field1, field2, field3, field4, field5, field6);
        return this;
    }

    @Override
    public <A, B, C, D, E, F, G> SelectStage select(SFunction<A, ?> field1, SFunction<B, ?> field2, SFunction<C, ?> field3, SFunction<D, ?> field4, SFunction<E, ?> field5, SFunction<F, ?> field6, SFunction<G, ?> field7) {
        Collections.addAll(selectFnFields, field1, field2, field3, field4, field5, field6, field7);
        return this;
    }

    /**
     * 支持跨表方法引用字段（惰性存储，buildSql 再解析）
     */
    @SafeVarargs
    public final <T> SelectStage select(SFunction<T, ?>... fields) {
        if (fields == null || fields.length == 0) {
            selectRawFields.add("*");
        } else {
            Collections.addAll(selectFnFields, fields);
        }
        return this;
    }

    /**
     * 原生字段（如 "count(1) as total"）
     */
    @Override
    public final SelectStage selectRaw(String... fields) {
        Collections.addAll(selectRawFields, fields);
        return this;
    }

    // ============================ FROM / JOIN ============================

    /**
     * from
     */
    @Override
    public FromStage from(Class<?> clazz) {
        this.mainTable = clazz;
        this.mainTableName = getTableName(clazz);
        this.mainAlias = getOrCreateAlias(clazz);
        return this;
    }

    /**
     * from 指定表别名
     */
    @Override
    public FromStage from(Class<?> clazz, String alias) {
        this.mainTable = clazz;
        this.mainTableName = getTableName(clazz);
        this.mainAlias = alias;
        this.tableAliases.put(clazz, alias);
        return this;
    }

    /**
     * from 使用字符串作为表名称
     */
    @Override
    public FromStage from(String table, String alias) {
        Class<?> clazz = C.get("TABLE-NAME-CLASS-CACHE::" + table);
        this.mainTable = clazz;
        this.mainTableName = table;
        this.mainAlias = alias;
        this.tableAliases.put(table, alias);
        return this;
    }

    /**
     * from 子查询
     */
    @Override
    public FromStage from(SelectBuilder subSelect, String alias) {
        this.mainTableSubQuery = subSelect;
        this.mainAlias = alias;
        return this;
    }

    /**
     * 左连接, 自动推断连接表的别名
     */
    @Override
    public FromStage leftJoin(Class<?> joinTable, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.LEFT, joinTable, getOrCreateAlias(joinTable), on);
    }

    /**
     * 左连接, 手动指定连接表的别名
     */
    @Override
    public FromStage leftJoin(Class<?> joinTable, String alias, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.LEFT, joinTable, alias, on);
    }

    /**
     * 左连接, 使用子查询的结果作为连接表
     */
    @Override
    public FromStage leftJoin(ISelect subSelect, String alias, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.LEFT, subSelect, alias, on);
    }

    /**
     * 右连接, 自动推断连接表的别名
     */
    @Override
    public FromStage rightJoin(Class<?> joinTable, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.RIGHT, joinTable, getOrCreateAlias(joinTable), on);
    }

    /**
     * 右连接, 手动指定连接表的别名
     */
    @Override
    public FromStage rightJoin(Class<?> joinTable, String alias, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.RIGHT, joinTable, getOrCreateAlias(joinTable), on);
    }

    /**
     * 右连接, 使用子查询的结果作为连接表
     */
    @Override
    public FromStage rightJoin(ISelect subSelect, String alias, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.RIGHT, subSelect, alias, on);
    }

    /**
     * 内连接, 自动推断连接表的别名
     */
    @Override
    public FromStage innerJoin(Class<?> joinTable, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.INNER, joinTable, getOrCreateAlias(joinTable), on);
    }

    /**
     * 内连接, 手动指定连接表的别名
     */
    @Override
    public FromStage innerJoin(Class<?> joinTable, String alias, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.INNER, joinTable, alias, on);
    }

    /**
     * 右连接, 使用子查询的结果作为连接表
     */
    @Override
    public FromStage innerJoin(ISelect subSelect, String alias, Consumer<JoinOnBuilder> on) {
        return join(JoinBuilder.JoinType.INNER, subSelect, alias, on);
    }

    /**
     * 通用表连接
     */
    private FromStage join(JoinBuilder.JoinType type, Class<?> joinClazz, String alias, Consumer<JoinOnBuilder> onBuilder) {
        // 构建on条件
        JoinOnBuilder builder = new JoinOnBuilder(tableAliases);
        // 注册表别名
        getOrCreateAlias(joinClazz, alias);
        onBuilder.accept(builder);
        String onClause = builder.toSql();
        List<Object> onParams = new ArrayList<>(builder.orderedParams());
        joins.add(new JoinBuilder(type, joinClazz, alias, onClause, onParams));
        return this;
    }

    /**
     * 通用表连接 (子查询版)
     */
    private SelectBuilder join(JoinBuilder.JoinType type, ISelect subSelect, String alias, Consumer<JoinOnBuilder> onBuilder) {
        JoinOnBuilder builder = new JoinOnBuilder(tableAliases);
        onBuilder.accept(builder);
        String onClause = builder.toSql();
        List<Object> onParams = new ArrayList<>(builder.orderedParams());
        joins.add(new JoinBuilder(type, subSelect, alias, onClause, onParams));
        return this;
    }

    // ============================ WHERE / HAVING ============================

    /**
     * where 条件
     */
    @Override
    public WhereStage where(Consumer<WhereBuilder> consumer) {
        // 如果consumer为空，查询全部数据
        if (consumer == null) {
            return this;
        }
        if (this.where == null) {
            this.where = new WhereBuilder(this.tableAliases);
        }
        consumer.accept(this.where);
        return this;
    }

    @Override
    public WhereStage where(WhereBuilder where) {
        this.where = where;
        return this;
    }

    /**
     * 分组
     */
    @Override
    public final <T> GroupByStage groupBy(SFunction<T, ?> field) { // 去掉 <T>
        Collections.addAll(groupByItems, field); // 先存 SFunction，惰性解析
        return this;
    }

    /**
     * 分组（原生）
     */
    @Override
    public GroupByStage groupByRaw(String... fields) {
        Collections.addAll(groupByItems, fields);
        return this;
    }

    /**
     * 分组过滤
     */
    @Override
    public HavingStage having(Consumer<HavingBuilder> consumer) {
        if (havingBuilder == null) havingBuilder = new HavingBuilder(this.tableAliases);
        consumer.accept(havingBuilder);
        return this;
    }

    /**
     * 升序排序
     */
    @Override
    public final <T> OrderStage orderByAsc(SFunction<T, ?> field) { // 去掉 <T>
        orderItems.add(new OrderItem(field, "ASC"));
        return this;
    }

    /**
     * 逆序排序
     */
    @Override
    public final <T> OrderStage orderByDesc(SFunction<T, ?> field) { // 去掉 <T>
        orderItems.add(new OrderItem(field, "DESC"));
        return this;
    }

    /**
     * 升序排序（原生）
     */
    @Override
    public OrderStage orderByAscRaw(String... fields) {
        for (String f : fields) orderItems.add(new OrderItem(f, "ASC"));
        return this;
    }

    /**
     * 降序排序（原生）
     */
    @Override
    public OrderStage orderByDescRaw(String... fields) {
        for (String f : fields) orderItems.add(new OrderItem(f, "DESC"));
        return this;
    }

    /*==================== 偏移 ========================*/
    @Override
    public LimitStage limit(Integer limit) {
        this.limit = limit;
        return this;
    }

    @Override
    public OffsetStage offset(Integer offset) {
        this.offset = offset;
        return this;
    }

    /*==================== 终结操作 ========================*/

    /**
     * 转为 SQL
     */
    @Override
    public String toSql() {
        orderedParams.clear();

        // 必须有 FROM
        if (mainTable == null && mainTableSubQuery == null) {
            SqlLogHelper.error("SELECT 缺少 FROM 表, 请检查");
            throw new IllegalStateException("SELECT 缺少 FROM 表，请检查");
        }

        // --- 1) 解析 SELECT（此时别名已在 from/join 建好） ---
        buildSelectFields();
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n\t").append(finalSelectFields.isEmpty() ? "*" : String.join(", ", finalSelectFields));
        // 如果 from 表是子查询
        if (mainTableSubQuery != null) {
            String fromSubSql = mainTableSubQuery.toSql()
                    .replaceAll("\n", " ")
                    .replaceAll("\t", " ")
                    .replaceAll("\\s+", " ")
                    .trim();

            sql.append("\nFROM\n\t(").append(fromSubSql).append(") ").append(mainAlias);
        }
        // 如果 from 表是单表
        else {
            sql.append("\nFROM\n\t").append(mainTableName).append(" ").append(mainAlias);
        }

        // --- 2) JOIN ---
        for (JoinBuilder join : joins) {
            sql.append("\n").append(join.toSql());
            orderedParams.addAll(join.orderedParams()); // JOIN 的 ? 参数在 WHERE 前
        }

        // --- 3) WHERE ---
        if (where != null) {
            String whereSql = where.toSql();
            if (!whereSql.isEmpty()) {
                sql.append(whereSql);
                orderedParams.addAll(where.orderedParams());
            }
        }

        // --- 4) GROUP BY（惰性解析） ---
        if (!groupByItems.isEmpty()) {
            List<String> groupByFields = new ArrayList<>();
            for (Object it : groupByItems) {
                if (it instanceof SFunction<?, ?>) {
                    groupByFields.add(resolveField((SFunction<?, ?>) it));
                } else {
                    groupByFields.add(it.toString());
                }
            }
            sql.append("\nGROUP BY\n\t").append(String.join(", ", groupByFields));
        }

        // --- 5) HAVING ---
        if (havingBuilder != null) {
            String havingSql = havingBuilder.toSql();
            if (!havingSql.isEmpty()) {
                sql.append("\n").append(havingSql);
                orderedParams.addAll(havingBuilder.orderedParams());
            }
        }

        // --- 6) ORDER BY（惰性解析） ---
        if (!orderItems.isEmpty()) {
            List<String> orderClauses = new ArrayList<>();
            for (OrderItem oi : orderItems) {
                String col = (oi.field instanceof SFunction)
                        ? resolveField((SFunction<?, ?>) oi.field)
                        : oi.field.toString();
                orderClauses.add(col + " " + oi.dir);
            }
            sql.append("\nORDER BY\n\t").append(String.join(", ", orderClauses));
        }

        // --- 7) LIMIT / OFFSET ---
        if (limit != null) {
            sql.append("\nLIMIT ").append(limit);
        }
        if (offset != null) {
            sql.append(" OFFSET ").append(offset);
        }

        return sql.toString();
    }

    private String toInnerSql() {
        buildSelectFields();
        List<Interceptor> interceptors = InterceptorManager.interceptors();
        for (Interceptor interceptor : interceptors) {
            interceptor.beforeSelectBuilder(mainTable, mainTableName, finalSelectFields, where);
        }
        return toSql();
    }

    /**
     * 获取符合条件的数据量
     */
    @Override
    public Long count() {
        // 清理排序和分页
        orderItems.clear();
        limit = null;
        offset = null;
        String sql = toInnerSql();
        String countSql = Strs.format("select count(1) as total from (\n{}\n) a", sql);

        DQLResult DQLResult = doSelect(
                () -> {
                    finalSelectFields = List.of("count(1)");
                    DQLResult result = SQLRuntime.doSelect(
                            countSql,
                            orderedParams
                    );
                    finalSelectFields = null;
                    return result;
                }, countSql
        );

        Map<String, Object> totalRow = DQLResult.getRows().getFirst();
        return Long.valueOf(totalRow.get("total").toString());
    }

    /**
     * 获取唯一数据，指定收集类型
     */
    @Override
    public <T> T one(Class<T> clazz) {
        String sql = toInnerSql();
        DQLResult DQLResult = doSelect(
                () -> SQLRuntime.doSelect(
                        sql,
                        orderedParams
                ), sql
        );

        return DQLResult.toBean(clazz);
    }

    /**
     * 获取唯一数据，使用当前主表作为收集类型
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T> T one() {
        return (T) one(mainTable);
    }

    /**
     * 获取符合条件的所有数据，收集成Map
     */
    @Override
    public List<Map<String, Object>> listMap() {
        String sql = toInnerSql();
        DQLResult DQLResult = doSelect(
                () -> SQLRuntime.doSelect(
                        sql,
                        orderedParams
                ), sql
        );

        return DQLResult.getRows();
    }

    /**
     * 获取符合条件的所有数据，指定收集类型
     */
    @Override
    public <T> List<T> list(Class<T> clazz) {
        String sql = toInnerSql();
        DQLResult DQLResult = doSelect(
                () -> SQLRuntime.doSelect(
                        sql,
                        orderedParams
                ), sql
        );

        return DQLResult.toBeans(clazz);
    }

    /**
     * 获取符合条件的所有数据，使用当前主表作为收集类型
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T> List<T> list() {
        return (List<T>) list(mainTable);
    }

    /**
     * 分页查询
     */
    @Override
    public <T> Page<T> page(Integer pageNo, Integer pageSize, Class<T> clazz) {
        // 1.计算总数量
        Long total = count();

        // 2.根据分页数据，计算offset和limit，并查询分页数据
        DQLResult pageResult = null;
        if (total > 0) {
            offset = (pageNo - 1) * pageSize;
            limit = pageSize;
            String sql = toInnerSql();
            pageResult = doSelect(
                    () -> SQLRuntime.doSelect(
                            toInnerSql(),
                            orderedParams
                    ), sql
            );
        }


        // 3.拼接Page对象
        Page<T> page = new Page<>();
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setPages((int) ((total + pageSize - 1) / pageSize));
        page.setTotal(total);
        if (pageResult == null) {
            page.setRecords(Collections.emptyList());
        } else {
            page.setRecords(pageResult.toBeans(clazz));
        }

        return page;
    }

    /**
     * 分页查询
     */
    @SuppressWarnings("unchecked")
    @Override
    public <T> Page<T> page(Integer pageNo, Integer pageSize) {
        return (Page<T>) page(pageNo, pageSize, mainTable);
    }

    /**
     * 获取有序的参数
     */
    @Override
    public List<Object> orderedParams() {
        return orderedParams;
    }

    private DQLResult doSelect(Supplier<DQLResult> supplier, String sql) {
        // SQL 执行前
        for (Interceptor interceptor : InterceptorManager.interceptors()) {
            interceptor.beforeSelect(mainTable, mainTableName, sql, finalSelectFields, where, orderedParams);
        }
        DQLResult sqlResult = supplier.get();

        // SQL 执行后
        for (Interceptor interceptor : InterceptorManager.interceptors()) {
            interceptor.afterSelect(mainTable, mainTableName, sql, finalSelectFields, where, orderedParams, sqlResult);
        }
        return sqlResult;
    }

    /**
     * 构建 SELECT 字段
     */
    private void buildSelectFields() {
        if (finalSelectFields != null) {
            return;
        }
        finalSelectFields = new ArrayList<>(selectRawFields);
        for (SFunction<?, ?> fn : selectFnFields) {
            finalSelectFields.add(resolveField(fn));
        }
    }

    /**
     * 获取表别名
     */
    private String getOrCreateAlias(Class<?> clazz) {
        return getOrCreateAlias(clazz, clazz.getSimpleName());
    }

    /**
     * 获取表别名
     */
    private String getOrCreateAlias(Class<?> clazz, String alias) {
        return tableAliases.computeIfAbsent(clazz, c -> Strs.toUnderline(alias));
    }
}
